# -*- coding: utf-8 -*-
# @Time    : 2025/5/9 15:37
# @Author  : cx
# @File    : sql_yu_ju.py
# @Software: PyCharm
# SQL语句命令保存

class SQL_YU_JI_l:
    # 设备类型表
    jb_zd_lx = """
            CREATE TABLE IF NOT EXISTS device_types (
            id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
            device_type TINYINT UNSIGNED NOT NULL COMMENT '设备类型 0-255', 
            type_name VARCHAR(50) NOT NULL COMMENT '设备类型名称',
            description VARCHAR(200) COMMENT '类型描述',
            INDEX idx_device_type (device_type)  
            )
          """
    # 设备信息表
    jb_zd_xing_xi = """
        CREATE TABLE IF NOT EXISTS devices (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
        device_code CHAR(12) NOT NULL UNIQUE COMMENT '设备唯一标识码,网卡地址', 
        device_type TINYINT UNSIGNED NOT NULL COMMENT '设备类型',           
        device_id VARCHAR(50) NOT NULL COMMENT '分配的ID',                 
        device_password VARCHAR(100) NOT NULL COMMENT '分配的密码',
        device_name VARCHAR(32) NOT NULL COMMENT '终端名称',
        device_shuo_ming VARCHAR(200) COMMENT '终端描述',          
        location TINYINT UNSIGNED COMMENT '设备安装位置',                        
        status TINYINT DEFAULT 0 COMMENT '设备状态: 0=离线, 1=在线, 2=故障',
        last_online_time TIMESTAMP NULL COMMENT '最后在线时间',
        device_token CHAR(32) NOT NULL UNIQUE COMMENT '32位握手令牌',
        device_shua_xin_token CHAR(32) NOT NULL UNIQUE COMMENT '32位刷新令牌',
        created_token TIMESTAMP NOT NULL  COMMENT '32位握手令牌下发时间',
        created_shua_xin_token TIMESTAMP NOT NULL  COMMENT '32位刷新令牌下发时间',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '初始连接时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '保留',
        FOREIGN KEY (device_type) REFERENCES device_types(device_type), 
        INDEX idx_status_time (device_code,device_name)  
        )
          """
    # 设备实时状态表
    jb_zd_zhuang_tai = """
            CREATE TABLE IF NOT EXISTS device_status (
        device_id INT UNSIGNED PRIMARY KEY,
        status TINYINT NOT NULL COMMENT '设备状态: 0=离线, 1=在线, 2=故障',
        last_heartbeat TIMESTAMP NOT NULL COMMENT '最后心跳时间',
        last_data_time TIMESTAMP NULL COMMENT '最后数据上传时间',
        signal_strength TINYINT COMMENT '信号强度',
        battery_level TINYINT COMMENT '电池电量(仅无线设备)',
        FOREIGN KEY (device_id) REFERENCES devices(id), 
        INDEX idx_status_time (status, last_heartbeat)  
    )
          """
    # 设备码
    jb_zd_ma = """
            CREATE TABLE IF NOT EXISTS device_ma (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        device_ma CHAR(32) NOT NULL UNIQUE COMMENT '32位设备编码',
        device_code CHAR(12) UNIQUE COMMENT '设备唯一标识码,网卡地址', 
        code_ji_huo TINYINT NOT NULL DEFAULT 0 COMMENT '是否激活,0 未激活,1 激活, 2 重新激活', 
        created_at TIMESTAMP NULL COMMENT '初始激活时间',
        INDEX idx_status_time (device_ma, device_code)  
    )
          """

    # 设备自定义
    jb_zd_zi_ding_yi = """
                CREATE TABLE IF NOT EXISTS device_zi_ding_yi (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            device_code CHAR(12) UNIQUE COMMENT '设备唯一标识码,网卡地址', 
            device_name VARCHAR(32) NOT NULL COMMENT '自定义终端名称',
            device_dai_hao TINYINT UNSIGNED NOT NULL COMMENT '自定义终端代号',
            device_type TINYINT UNSIGNED NOT NULL COMMENT '设备类型',
            location TINYINT UNSIGNED COMMENT '设备安装位置', 
            FOREIGN KEY (device_code) REFERENCES devices(device_code), 
            INDEX idx_status_time (device_name, device_code)  
        )
              """
    # 位置表
    jb_zd_rooms = """
                  CREATE TABLE IF NOT EXISTS device_rooms (
              id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
              room_name VARCHAR(32) NOT NULL COMMENT '位置名称',
              house_id TINYINT UNSIGNED COMMENT '所属位置id', 
              INDEX idx_device_type (room_name)    
          )
                """

    # 温湿度传感器历史数据表（按年分区）
    jb_zd_wen_shi_du = """
            CREATE TABLE IF NOT EXISTS sensor_temperature_humidity (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        device_id INT UNSIGNED NOT NULL,
        temperature DECIMAL(5,2) NOT NULL COMMENT '温度值(摄氏度)',
        humidity DECIMAL(5,2) NOT NULL COMMENT '湿度值(百分比)',
        record_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices(id),
        INDEX idx_device_time (device_id, record_time)  
    ) PARTITION BY RANGE (YEAR(record_time)) (
        PARTITION p2023 VALUES LESS THAN (2024),
        PARTITION p2024 VALUES LESS THAN (2025),
        PARTITION p2025 VALUES LESS THAN (2026)
    )
          """
    # 风量传感器历史数据表
    jb_zd_fang_liang = """
            CREATE TABLE IF NOT EXISTS sensor_wind (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        device_id INT UNSIGNED NOT NULL,
        wind_speed DECIMAL(5,2) NOT NULL COMMENT '风速(m/s)',
        wind_direction DECIMAL(6,2) NOT NULL COMMENT '风向(角度,0-360)',
        record_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices(id),
        INDEX idx_device_time (device_id, record_time)  
    )
          """
    # 雨滴传感器历史数据表
    jb_zd_yu_di = """
            CREATE TABLE IF NOT EXISTS sensor_rain (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        device_id INT UNSIGNED NOT NULL,
        is_raining TINYINT(1) NOT NULL COMMENT '是否下雨: 0=否, 1=是',
        rainfall DECIMAL(6,2) DEFAULT 0 COMMENT '降雨量(mm)',
        record_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices(id),
        INDEX idx_device_time (device_id, record_time)  
    )
          """
    # 远程开关历史状态表
    jb_zd_kai_guan = """
            CREATE TABLE IF NOT EXISTS switch_status_history (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        device_id INT UNSIGNED NOT NULL,
        device_name VARCHAR(50) NOT NULL COMMENT '终端名称',
        device_shuo_ming VARCHAR(200) COMMENT '终端描述',
        switch_state TINYINT NOT NULL COMMENT '开关状态: 0=关闭, 1=打开',
        operation_type TINYINT DEFAULT 0 COMMENT '操作类型: 0=自动, 1=手动',
        operation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices(id),
        INDEX idx_device_time (device_id, operation_time,device_name)  
    )
          """

    mqtt_shujuku ="""
    CREATE TABLE `mqtt_user` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(100) DEFAULT NULL COMMENT '用户',
     `password_hash` varchar(100) DEFAULT NULL COMMENT '密码',
     `salt` varchar(35) DEFAULT NULL COMMENT '可选，为空或不存在时视为空盐',
     `is_superuser` tinyint(1) DEFAULT 0 COMMENT '可选，标记当前客户端是否为超级用户，默认为 false',
     `created` datetime DEFAULT NULL COMMENT '...',
     PRIMARY KEY (`id`),
     UNIQUE KEY `mqtt_username` (`username`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """


# 添加一名用户名为 emqx_u、密码为 public、盐值为 slat_foo123、散列方式为 sha256 且超级用户标志为 true 的用户
    mqtt_tian_jia="""
        INSERT INTO mqtt_user(username, password_hash, salt, is_superuser) VALUES ('cx001', SHA2(concat('cx123100', 'slat_foo123'), 256), 'slat_foo123', 1);
    """
    mqtt_cha_xun="""
        SELECT password_hash, salt, is_superuser FROM mqtt_user WHERE username = %s LIMIT 1
    """


    def __init__(self):
        pass

sql = SQL_YU_JI_l()
ss = sql.jb_zd_lx




"""
一、数据库设计思路
设备管理表：存储所有设备的基础信息（设备码、认证信息、类型等）
实时状态表：记录每个设备的当前状态（在线 / 离线、开关状态等）
历史数据表：按设备类型拆分，存储高频上传的传感器数据
优化策略：使用分区表、时间戳索引和适当的数据类型，确保高频写入性能

三、设计要点解释
1. 设备管理模块
设备类型表：通过预定义设备类型，便于扩展新设备类型
设备信息表：存储设备基础信息和认证凭证，支持设备的增删改查
设备状态表：实时跟踪设备在线状态和连接质量
2. 传感器数据存储
按类型拆分表：将不同类型的传感器数据分开存储，避免单表数据过大
分区表：对高频写入的温湿度数据按年分区，提高查询和维护效率
优化的数据类型：使用 DECIMAL 存储精确数值，避免浮点数精度问题
3. 高频写入优化
复合索引：为 (device_id, record_time) 添加索引，加速按设备和时间的查询
BIGINT 主键：使用 BIGINT 作为自增主键，避免在高频写入时出现主键耗尽
TIMESTAMP 默认值：利用 DEFAULT CURRENT_TIMESTAMP 减少客户端时间处理
4. 扩展性考虑
设备类型可扩展：通过 device_types 表轻松添加新设备类型
状态字段预留：设备状态和操作类型使用 TINYINT 类型，便于后续扩展状态值


"""
"""


-- 设备类型表
CREATE TABLE IF NOT EXISTS device_types (
    id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(50) NOT NULL COMMENT '设备类型名称',
    description VARCHAR(200) COMMENT '类型描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 设备信息表
CREATE TABLE IF NOT EXISTS devices (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    device_code VARCHAR(50) NOT NULL UNIQUE COMMENT '设备唯一标识码',
    device_type TINYINT UNSIGNED NOT NULL COMMENT '设备类型',
    device_id VARCHAR(50) NOT NULL COMMENT '分配的ID',
    device_password VARCHAR(100) NOT NULL COMMENT '分配的密码',
    location VARCHAR(100) COMMENT '设备安装位置',
    status TINYINT DEFAULT 0 COMMENT '设备状态: 0=离线, 1=在线, 2=故障',
    last_online_time TIMESTAMP NULL COMMENT '最后在线时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_type) REFERENCES device_types(id),
    INDEX idx_device_code (device_code)  -- 加速设备码查询
);

-- 设备实时状态表
CREATE TABLE IF NOT EXISTS device_status (
    device_id INT UNSIGNED PRIMARY KEY,
    status TINYINT NOT NULL COMMENT '设备状态: 0=离线, 1=在线, 2=故障',
    last_heartbeat TIMESTAMP NOT NULL COMMENT '最后心跳时间',
    last_data_time TIMESTAMP NULL COMMENT '最后数据上传时间',
    signal_strength TINYINT COMMENT '信号强度',
    battery_level TINYINT COMMENT '电池电量(仅无线设备)',
    FOREIGN KEY (device_id) REFERENCES devices(id), -- 外键约束
    INDEX idx_status_time (status, last_heartbeat)  -- 加速在线设备查询
);

-- 温湿度传感器历史数据表（按年分区）
CREATE TABLE IF NOT EXISTS sensor_temperature_humidity (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    device_id INT UNSIGNED NOT NULL,
    temperature DECIMAL(5,2) NOT NULL COMMENT '温度值(摄氏度)',
    humidity DECIMAL(5,2) NOT NULL COMMENT '湿度值(百分比)',
    record_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    INDEX idx_device_time (device_id, record_time)  -- 复合索引：设备ID+时间
) PARTITION BY RANGE (YEAR(record_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 风量传感器历史数据表
CREATE TABLE IF NOT EXISTS sensor_wind (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    device_id INT UNSIGNED NOT NULL,
    wind_speed DECIMAL(5,2) NOT NULL COMMENT '风速(m/s)',
    wind_direction DECIMAL(6,2) NOT NULL COMMENT '风向(角度,0-360)',
    record_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    INDEX idx_device_time (device_id, record_time)  -- 复合索引
);

-- 雨滴传感器历史数据表
CREATE TABLE IF NOT EXISTS sensor_rain (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    device_id INT UNSIGNED NOT NULL,
    is_raining TINYINT(1) NOT NULL COMMENT '是否下雨: 0=否, 1=是',
    rainfall DECIMAL(6,2) DEFAULT 0 COMMENT '降雨量(mm)',
    record_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    INDEX idx_device_time (device_id, record_time)  -- 复合索引
);

-- 远程开关历史状态表
CREATE TABLE IF NOT EXISTS switch_status_history (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    device_id INT UNSIGNED NOT NULL,
    switch_state TINYINT(1) NOT NULL COMMENT '开关状态: 0=关闭, 1=打开',
    operation_type TINYINT DEFAULT 0 COMMENT '操作类型: 0=自动, 1=手动',
    operation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    INDEX idx_device_time (device_id, operation_time)  -- 复合索引
);
"""